package net.blogways.ordermgr.common.command.dbquery;

import com.asiainfo.bpc.utils.ToolFactory;
import net.blogways.ordermgr.util.Constants;
import net.blogways.ordermgr.util.RescodeConstants;
import com.asiainfo.utils.Utils;
import org.apache.commons.chain.Command;
import org.apache.commons.chain.Context;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by chenfan on 2017/7/17.
 */
@Component("common:queryselect2")
public class QuerySelect2Command implements Command {
  private final Logger log = LoggerFactory.getLogger(getClass());

  @Override
  public boolean execute(Context context) throws Exception {
    return Command.CONTINUE_PROCESSING;
  }

  public Boolean getSelect2(Context context) throws Exception {
    log.info("context = " + context);
    String tableName = ((String) context.get("tbl")).toUpperCase();
    List projections = (ArrayList) context.get("projections");
    String operateAcctId = (String) context.get("OPERATE_ACCT_ID");
    String dbRoute;
    if (context.containsKey("dbRoute") && !"".equals(context.get("dbRoute"))) {
      dbRoute = (String) context.get("dbRoute");
    } else {
      dbRoute = Constants.DATABASE_CEN;
    }
    String page = context.get("pages") == null ? "1" : (String) context.get("pages").toString();
    Long pages = Long.parseLong(page);
    Long row = 0l;
    if (context.get("row") != null){
      row = Long.valueOf(context.get("row").toString());
    }
    String sql = "select ";

    if (projections.size() > 0) {
      for (int i = 0; i < projections.size(); i++) {
        sql += "concat(" + (String) projections.get(i) + ",'') AS " + (String) projections.get(i) + " , ";
      }
      sql = sql.substring(0, sql.length() - 2);
    } else {
      Utils.returnFalseResult(context, "E301", "empty_output_field.");
    }
    log.info("sql= " + sql);

    if ("".equals(tableName)) {
      Utils.returnFalseResult(context, "E302", "empty_table_name");
    } else {
      sql += " from " + tableName;
    }
    String where = " where 1=1 ";
    sql += where;

    Map params = new HashMap();

    if (context.containsKey("tableRef")) {
      if (!((Map) context.get("tableRef")).isEmpty()) {
        Map tableRef = (Map) context.get("tableRef");
        String ref = "SEL_SELECT2_" + (String) tableRef.get("ref");
        params = tableRef.get("params") == null ? new HashMap() : (Map) tableRef.get("params");
        if (null != params.get("row")) {
          row = Long.valueOf(params.get("row").toString());
        }
        Map send = new HashMap<>();
        send.put("tabName", tableName);
        send.put("sqlRef", ref);
        List rs = ToolFactory.getInstance().getSqlCodeService(Constants.DATABASE_CEN)
                .select("SELECT SQL_STMT FROM code_code WHERE tab_name= :tabName AND sql_ref = :sqlRef", send);
        log.debug("rs=" + rs);

        if (rs.size() <= 0) {
          return Utils.returnFalseResult(context, "E401", "miss_query_info");
        }
        sql = (String) ((Map) rs.get(0)).get("SQL_STMT");
      }
    }


    log.info("sql= " + sql);

    if (context.containsKey("conditions")) {
      Map<String, Object> conditions = (Map<String, Object>) context.get("conditions");
      for (String key : conditions.keySet()) {
        sql += " and " + key + " =:V" + key;
        params.put("V" + key, URLDecoder.decode(conditions.get(key).toString(), "UTF-8"));
      }
    }

    if (context.containsKey("comparelist")) {
      List<Map<String, Object>> comparelist = (List<Map<String, Object>>) context.get("comparelist");
      for (Map<String, Object> map : comparelist) {
        String symbol = (String) map.get("SYMBOL");
        String sign = " " + symbol + " ";
        sql += " and " + map.get("LEFT") + sign + ":V" + map.get("LEFT");
        String right = map.get("RIGHT").toString();
        if (right.contains("coded`")) {
          right = URLDecoder.decode(right.split("`")[1], "UTF-8");
        }
        params.put("V" + map.get("LEFT"), right);
      }
    }

    String tempSql=null;
    if (context.containsKey("search")) {
      Map<String, Object> search = (Map<String, Object>) context.get("search");
      for (String key : search.keySet()) {

          String searchValue = search.get(key).toString();
          if (searchValue.contains("coded`")) {
            searchValue = URLDecoder.decode(searchValue.split("`")[1], "UTF-8");
          }
          if (sql(searchValue)) {
            sql += " and " + key + "  LIKE binary \"%" + searchValue + "%\"";
          } else {
            return Utils.returnFalseResult(context, RescodeConstants.ILLEGAL_CHARACTER, "查询包含非法参数");
          }
      }
    }

    if (context.containsKey("islocale")) {
      String islocale = (String) context.get("islocale");
      if ("Y".equals(islocale.toUpperCase())) {
        String locale = (String) context.get("locale");
        sql += " and LOCALE=:VLOCALE";
        params.put("VLOCALE", locale);
      }
    }

    if (context.containsKey("startEnd")) {
      if (Long.valueOf(context.get("startEnd").toString()) == 1) {
        sql += " and now() between START_DATE and END_DATE";
      }
    }

    if (context.containsKey("startEndTime")) {
      if (Long.valueOf(context.get("startEndTime").toString()) == 1) {
        sql += " and START_TIME <= now() and END_TIME >= now()";
      }
    }

    if (context.containsKey("groupBy")) {
      Map<String, Object> group = (Map<String, Object>) context.get("groupBy");
      if (!group.isEmpty()) {
        sql += " GROUP BY ";
        for (String key : group.keySet()) {
          sql += " " + key + ", ";
        }
        sql = sql.substring(0, sql.length() - 2);
      }
    }

    Long start = (pages - 1) * row;
    Long end = row;
    sql += " LIMIT " + start + ", " + end;

    log.debug("************ sql = {} ************", sql);


    List l = new ArrayList();

    params.put("OPERATE_ACCT_ID", operateAcctId);
    l = ToolFactory.getInstance().getSqlCodeService(dbRoute).select(sql, params);
    log.info("l=" + l);
    return Utils.returnOk(context, l);
  }

  /**
   * 防止sql注入.
   *
   * @param str
   * @return
   */
  public static boolean sql(String str) throws Exception {
    String reg = "(?i).*([';]+|(--)+|OR|and|exec|insert|select|delete|updatecount|%|chr|mid|master|truncate|char|declare).*";
    if (str.matches(reg)) {
      return false;
    }
    return true;
  }



}
